<?php

namespace app\index\controller;

use excel\ExcelDownload;
use excel\ExcelReader;
use think\Db;
use think\Validate;

class Ashippingmanagement extends Common
{
    //一级出货数据管理
    public function index()
    {
        //查询条件
        $where = $this->request->post();
        $this->assign('where', $where);
        $where = self::str($where);
        $page = empty($where['pages']) ? 1 : $where['pages'];
        $list = empty($where['limts']) ? 10 : $where['limts'];
        unset($where['pages']);
        unset($where['limts']);
        //总数量，总金额，总条数
        $sumCount = Db::table('bx_fy')->alias('a')
            ->join('cg_jxs b', 'a.sdAgency_id = b.id')
            ->join('cg_net c', 'a.sdNet_id = c.id', 'LEFT')
            ->join('pro_products d', 'a.product_id = d.id')
            ->join('employee e', 'a.salesman_id = e.id')
            ->join('cg_market_city f', 'c.city = f.id', 'LEFT')
            ->join('cg_market_provice g', 'c.provice = g.id', 'LEFT')
            ->join('cg_market_zoning h', 'c.zoning = h.id', 'LEFT')
            ->where($where)
            ->field('sum(fyNum) fyNum,sum(fyZheMoney) fyZheMoney,count(*) count')->find();
        $this->assign('sumCount', $sumCount);
        //分页
        $maxpage = ceil($sumCount['count'] / $list);
        $page = $page < $maxpage ? $page : $maxpage;
        $page = $page > 1 ? $page : 1;
        $start = ($page - 1) * $list;
        $this->assign('page', $page);
        $this->assign('maxpage', $maxpage);
        $this->assign('limit', $list);
        //数据
        $data = Db::table('bx_fy')->alias('a')
            ->join('cg_jxs b', 'a.sdAgency_id = b.id')
            ->join('cg_net c', 'a.sdNet_id = c.id', 'LEFT')
            ->join('pro_products d', 'a.product_id = d.id')
            ->join('employee e', 'a.salesman_id = e.id')
            ->join('cg_market_city f', 'c.city = f.id', 'LEFT')
            ->join('cg_market_provice g', 'c.provice = g.id', 'LEFT')
            ->join('cg_market_zoning h', 'c.zoning = h.id', 'LEFT')
            ->field('a.*,b.title jxs,b.sn jxsNo,c.title wd,c.sn wdNo,d.pro_name,d.pro_no,e.em_name,f.title shi,g.title sheng,h.title qu')
            ->where($where)->order('id', 'desc')
            ->limit($start, $list)
            ->select();
        $this->assign('data', $data);
        return $this->fetch('ashippingmanagement/index');
    }

    //替换_
    public function str($where)
    {
        $tmp = [];
        $like = [
            'b_sn' => ['like', '%val%'],
            'b_title' => ['like', '%val%'],
            'c_sn' => ['like', '%val%'],
            'c_title' => ['like', '%val%'],
            'd_pro_no' => ['like', '%val%'],
            'd_pro_name' => ['like', '%val%'],
            'h_title' => ['like', '%val%'],
            'g_title' => ['like', '%val%'],
            'f_title' => ['like', '%val%'],
            'pages' => 'val',
            'limts' => 'val',
            'a_fyType' => 'val',//发运类型
            'l_brand_name' => ['like', '%val%'],//品牌
            'k_category_name' => ['like', '%val%'],//品类
            'j_series_name' => ['like', '%val%'],//系列
            'i_sku_name' => ['like', '%val%'],//SKU
            'b_em_name' => ['like', '%val%'],
            'a_sn' => ['like', '%val%'],
            'j_title' => ['like', '%val%'],
            'o_material_no' => ['like', '%val%'],
            'o_material_name' => ['like', '%val%'],
            'c_kind' => ['like', '%val%'],
            'a_shr' => ['like', '%val%'],
            'd_gdrq' => ['like', '%val%'],

        ];
        foreach ($where as $key => $val) {
            if (!empty($val)) {
                if(isset($where['a_fyDate']) && isset($where['a_fyDate1'])){
                    if ($where['a_fyDate'] && $where['a_fyDate1']) {
                        $tmp['a.fyDate'] = ['between', [$where['a_fyDate'], $where['a_fyDate1']]];
                    } else if ($where['a_fyDate']) {
                        $tmp['a.fyDate'] = ['>=', $where['a_fyDate']];
                    } else if ($where['a_fyDate1']) {
                        $tmp['a.fyDate'] = ['<=', $where['a_fyDate1']];
                    }
                }
                foreach ($like as $k => $v) {
                    if ($key == $k) {
                        $tmp[preg_replace('/\_/', '.', $key, 1)] = str_replace('val', $val, $v);
                    }
                }

            }
            unset($tmp['items']);

        }
        return $tmp;
    }

    //新增一级出货页面
    public function addP()
    {
        return $this->fetch('ashippingmanagement/addStship');
    }

    //新增一级发运数据
    public function add()
    {
        $data = $this->request->post();
        //注：如果发运开票金额为0切发运数量不为0的发运数据为搭赠发运，即fyType = 1;
        if ($data['fyZheMoney'] == 0 && $data['fyNum'] != 0) $data['fyType'] = 1;
        $validate = new Validate([
            ['fyDate', 'require|date', '发运日期必须|日期格式'],
            ['sdAgency', 'require', '经销商必须'],
            ['sdAgency_id', 'require', '经销商必须'],
            ['product', 'require', '产品名称必须'],
            ['product_id', 'require', '产品名称必须'],
            ['fyNum', 'require|number', '数量必须|数字格式'],
            ['fyZheMoney', 'require|number', '发运开票折后金额必须|数字格式'],
            ['orderNumber', 'require', '订单号必须'],
            ['fyONumber', 'require', '发运单号必须'],
            ['salesman', 'require', '销售人员必须'],
            ['salesman_id', 'require', '销售人员必须'],
        ]);
        if ($validate->check($data)) {
            $res=Db::table('bx_fy')->insertGetId($data);
            if ($res) {
                $sdata=["type"=>1,"fydate"=>$data["fyDate"],"fid"=>$res,"jid"=>$data["sdAgency_id"],"sid"=>null,"nid"=>$data["sdNet_id"],"pid"=>$data["product_id"],"cman"=>$data["salesman"]];
                db("bx_fy_huizong")->insert($sdata);
                return json([
                    "success" => true,
                ]);
            } else {
                return json([
                    "success" => false,
                    'mag' => '网络错误！请重试'
                ]);
            }
        } else {
            return json([
                "success" => $validate->check($data),
                "msg" => $validate->getError()
            ]);
        }
    }

    //选择经销商页面
    public function selectJXS()
    {
        return $this->fetch('ashippingmanagement/selectJXS');
    }

    //选择产品页面
    public function selectProduct()
    {
        return $this->fetch('ashippingmanagement/selectProduct');
    }

    //删除所选数据
    public function delAll()
    {
        $where = $this->request->post('id');
        if (Db::table('bx_fy')->where("id in ($where)")->delete()) {
            return json([
                "success" => true
            ]);
        } else {
            return json([
                "success" => false,
                'mag' => '网络错误！请重试'
            ]);
        }
    }

    //删除所选数据
    public function del()
    {
        if (Db::table('bx_fy')->where('id', $this->request->post('id'))->delete()) {
            db("bx_fy_huizong")->where(["fid"=>$this->request->post('id'),"type"=>1])->delete();
            return json([
                "success" => true
            ]);
        } else {
            return json([
                "success" => false,
                'mag' => '网络错误！请重试'
            ]);
        }
    }

    //编辑页面
    public function editP()
    {
        $id = $this->request->get('id');
        if ($id) {
            $data = Db::table('bx_fy')->alias('a')
                ->join('cg_jxs b', 'a.sdAgency_id = b.id')
                ->join('cg_net c', 'a.sdNet_id = c.id', 'LEFT')
                ->join('pro_products d', 'a.product_id = d.id')
                ->join('employee e', 'a.salesman_id = e.id')
                ->field('a.*,b.title jxs,c.title wd,d.pro_name,e.em_name')
                ->where('a.id', $id)
                ->find();
            $this->assign('data', $data);
            return $this->fetch('ashippingmanagement/edit');
        }

    }

    //编辑
    public function edit()
    {
        $data = $this->request->post();
        //注：如果发运开票金额为0切发运数量不为0的发运数据为搭赠发运，即fyType = 1;
        if ($data['fyZheMoney'] == 0 && $data['fyNum'] != 0) $data['fyType'] = 1;
        $validate = new Validate([
            ['id', 'require', '主键必须'],
            ['fyDate', 'require|date', '发运日期必须|日期格式'],
            ['sdAgency', 'require', '经销商必须'],
            ['sdAgency_id', 'require', '经销商必须'],
            ['product', 'require', '产品名称必须'],
            ['product_id', 'require', '产品名称必须'],
            ['fyNum', 'require|number', '数量必须|数字格式'],
            ['fyZheMoney', 'require|number', '发运开票折后金额必须|数字格式'],
            ['orderNumber', 'require', '订单号必须'],
            ['fyONumber', 'require', '发运单号必须'],
            ['salesman', 'require', '销售人员必须'],
            ['salesman_id', 'require', '销售人员必须'],
        ]);
        if ($validate->check($data)) {
            Db::table('bx_fy')->where('id', $data['id'])->update($data);
            $sdata=["fydate"=>$data["fyDate"],"jid"=>$data["sdAgency_id"],"sid"=>null,"nid"=>$data["sdNet_id"],"pid"=>$data["product_id"],"cman"=>$data["salesman"]];
            db("bx_fy_huizong")->where(["fid"=>$data['id'],"type"=>1])->update($sdata);
            return json([
                "success" => true,
            ]);
        } else {
            return json([
                "success" => $validate->check($data),
                "msg" => $validate->getError()
            ]);
        }
    }

    //导入
    public function import()
    {
        $data['ny'] = date('Y年m月');
        $firstDate = date('Y-m-') . '01';
        $lastDate = date('Y-m-d', strtotime("$firstDate +1 month -1 day"));
        $data['sum'] = Db::table('bx_fy')
            ->where('fyDate', 'between', [$firstDate, $lastDate])
            ->field('sum(fyZheMoney) fyZheMoney,count(*) count')
            ->find();
        $data['user'] = Db::table('employee')->where('id', session('employee_id'))->value('em_name');
        $data['date'] = Db::table('bx_fy_log')->field('date,operator')->order('date', 'desc')->limit(1)->find();
        if (!$data['date']) {
            $data['date'] = ['date' => $firstDate . ' 00:00:00', 'operator' => $data['user']];
        }
        $this->assign('data', $data);
        return $this->fetch();
    }

    public function upload()
    {
        // 获取表单上传文件
        $file = request()->file('file');
        // 移动到框架应用根目录/public/uploads/bx_fy 目录下
        $info = $file->validate(['ext' => 'xls,xlsx', 'size' => 105678])->move(ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_fy');
        if ($info) {
            // 成功上传后 获取上传信息
            $data = self::testExcel2($info->getSaveName());
            echo "<script>top.sweetAlert('" . $data['msg'] . "','','" . $data['success'] . "');parent.location.reload();</script>";
        } else {
            // 上传失败获取错误信息
            echo "<script>top.sweetAlert('" . $file->getError() . "','','warning');</script>";
        }
    }

    /**
     * excel下载示例
     */
    public function testExcel()
    {
        $titles = ['姓名', '年龄', '性别', '身高'];
        // titles格式也可以如下
//        $titles = [
//            'F1' => '姓名',
//            'F2' => '年龄',
//            'F3' => '性别',
//            'F4' => '身高',
//        ];

        $data = [
            ['jason', 28, 'male', 180],
            ['lotus', 18, 'female', 170],
            ['jack', 20, 'male', 188],
        ];
        $excel = new ExcelDownload();
        $excel->exec($data, $titles, 'Excel测试', ['sheet' => ['rowHeight' => 20]]);
    }

    //导出一级出货数据
    public function export()
    {
        $user = Db::table('employee')->where('id', session('employee_id'))->value('em_name');
        $str = '注：本表单由销路通软件导出，导出时间：' . date('Y-m-d H:i:s') . '；导出人：' . $user . '；金额单位：元';
        $str1 = '一级出货数据汇总';
//        $titles = ['姓名', '年龄', '性别', '身高'];
        // titles格式也可以如下
        $titles = [
            'No' => 'No.',
            'qu' => '分区',
            'sheng' => '省份',
            'shi' => '城市',
            'jxsNo' => '售达方经销商编号',
            'jsx' => '售达方经销商名称',
            'wdNo' => '售达方网点编号',
            'wd' => '售达方网点名称',
            'pro_no' => '产品编码',
            'pro_name' => '产品称号',
            'fyNum' => '数量',
            'fyBasicUnit' => '发运基本单位',
            'fyZheMoney' => '发运开票金额',
            'fyMaoMoney' => '毛发运金额',
            'fyJingMoney' => '净发运金额',
            'fyDate' => '发运日期',
            'orderNumber' => '订单号',
            'fyONumber' => '发运单号',
            'des' => '批号/批次',
            'batchNumber' => '备注',
            'em_name' => '销售人员',
        ];
        Db::table('bx_fy')->query("set @i = 0");//数据库自动增加序号
        $data = Db::table('bx_fy')->alias('a')
            ->join('cg_jxs b', 'a.sdAgency_id = b.id')
            ->join('cg_net c', 'a.sdNet_id = c.id', 'LEFT')
            ->join('pro_products d', 'a.product_id = d.id')
            ->join('employee e', 'a.salesman_id = e.id')
            ->join('cg_market_city f', 'c.city = f.id', 'LEFT')
            ->join('cg_market_provice g', 'c.provice = g.id', 'LEFT')
            ->join('cg_market_zoning h', 'c.zoning = h.id', 'LEFT')
            ->field('(@i:=@i+1) No,h.title qu,g.title sheng,f.title shi,b.sn jxsNo,b.title jxs,c.sn wdNo,c.title wd,d.pro_no,d.pro_name,
            a.fyNum,a.fyBasicUnit,a.fyZheMoney,a.fyMaoMoney,a.fyJingMoney,a.fyDate,a.orderNumber,a.fyONumber,a.des,a.batchNumber,e.em_name')
            ->select();
        $data[] = [
            'title' => $str1,
            'sub' => $str,
        ];
        $excel = new ExcelDownload();
        $excel->exec($data, $titles, '一级出货数据', ['sheet' => ['rowHeight' => 20]], 3);
    }

    /**
     * excel导入示例
     */
    public function testExcel2($file)
    {
        $url = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_fy' . DS . $file;
        $reader = new ExcelReader($url);
        $titlesMap = [
            'sdAgency' => '售达方经销商编号',
            'sdNet' => '售达方网点编号',
            'product' => '产品编码',
            'fyNum' => '数量',
            'fyZheMoney' => '发运开票金额',
            'fyMaoMoney' => '毛发运金额',
            'fyJingMoney' => '净发运金额',
            'fyDate' => '发运日期',
            'orderNumber' => '订单号',
            'fyONumber' => '发运单号',
            'salesman' => '销售人员',
            'batchNumber' => '批号/批次',
            'des' => '备注',
        ];
        // 是否提供titlesMap 返回的数据结构不一致
        $data = $reader->exec($titlesMap);
        if ($data) {
            unset($data[0]);
            foreach ($data as $key => $val) {
                $is_empty = 0;
                foreach ($val as $k => $v) {
                    if (!trim($v)) {
                        $is_empty++;
                    }
                }
                if ($is_empty == count($val)) unset($data[$key]);
            }

            if ($data) {
                //经销商
                $jx = Db::table('cg_jxs')->column('id,sn');
                $jx1 = implode(',', $jx);

                //产品
                $cp = Db::table('pro_products')->column('id,pro_no');
                $cp1 = implode(',', $cp);
                //销售人员
                $ry = Db::table('employee')->column('id,em_name');
                $ry1 = implode(',', $ry);

                //
                //时间范围
                $firstDate = date('Y-m-') . '01';
                $lastDate = date('Y-m-d', strtotime("$firstDate +1 month -1 day"));
                $firstDates=date('Y-m-d', strtotime("$firstDate -1 day"));
                $lastDates=date('Y-m-d', strtotime("$firstDate +1 month"));
                $validate = new Validate([
                    ['sdAgency', 'require|in:' . $jx1, '经销商必须|经销商不存在'],
                    ['product', 'require|in:' . $cp1, '产品名称必须|产品不存在'],
                    ['fyNum', 'require|number', '数量必须|数字格式'],
                    ['fyZheMoney', 'require|number', '发运开票折后金额必须|数字格式'],
                    ['fyDate', 'require|date|between:' . $firstDate . ',' . $lastDate . '', '发运日期必须|日期格式|发运日期必须在' . $firstDate . '之间' . $lastDate],
                    ['orderNumber', 'require', '订单号必须'],
                    ['fyONumber', 'require', '发运单号必须'],
                    ['salesman', 'require|in:' . $ry1, '销售人员必须|销售人员不存在'],
                ]);
                $off = 1;
                $m = '';//折后金额总额
               

                foreach ($data as $key => $val) {
                    if ($data[$key]['fyZheMoney'] == 0 && $data[$key]['fyNum'] != 0) $data[$key]['fyType'] = 1;
                    $m += $data[$key]['fyZheMoney'];
                    $data[$key]['sdAgency_id'] = array_search($data[$key]['sdAgency'], $jx);
                    
                    $data[$key]['product_id'] = array_search($data[$key]['product'], $cp);
                    
                    $data[$key]['salesman_id'] = array_search($data[$key]['salesman'], $ry);
                    if(trim($data[$key]["sdNet"])){
                        $net=db("cg_net")->where(["sn"=>trim($data[$key]["sdNet"])])->field("id,sn,title")->find();
                        if($net){
                            $data[$key]["sdNet"]=$net["title"];
                            $data[$key]["sdNet_id"]=$net["id"];
                        }else{
                             return [
                                "success" => 'warning',
                                'msg' => '网点不存在'
                            ];
                        }
                    }else{
                        $data[$key]["sdNet"]='';
                        $data[$key]["sdNet_id"]=0;
                    }
                    if ($validate->check($data[$key])) {
                        $off = 1;
                    } else {
                        $off = 0;
                        break;
                    }
                    $data[$key]['sdAgency']=db("cg_jxs")->where(["sn"=>$data[$key]['sdAgency']])->value("title");
                    $data[$key]['product']=db("pro_products")->where(["pro_no"=>$data[$key]['product']])->value("pro_name");
                }
                if ($off === 1) {
                    $tmp = [
                        'operator' => Db::table('employee')->where('id', session('employee_id'))->value('em_name'),
                        'log' => '导入了' . date('Y日m月') . '的发运数据',
                        'file' => $file,
                        'date' => date('Y-m-d H:i:s'),
                        'ny' => date('Y-m'),
                        'rows' => count($data),
                        'zmoney' => $m
                    ];

                    foreach ($data as $k => $v) {
                        # code...
                        $tmp1=db("bx_fy")->insertGetId($v);
                        $sdata=["type"=>1,"fydate"=>$data[$k]["fyDate"],"fid"=>$tmp1,"jid"=>$data[$k]["sdAgency_id"],"sid"=>null,"nid"=>$data[$k]["sdNet_id"],"pid"=>$data[$k]["product_id"],"cman"=>$data[$k]["salesman"]];
                        db("bx_fy_huizong")->insert($sdata);
                    }
                   // if (Db::table('bx_fy')->insertAll($data)) {
                        if (Db::table('bx_fy_log')->insert($tmp)) {
                            return [
                                "success" => 'success',
                                'msg' => '上传文件成功'
                            ];
                        } else {
                            return [
                                "success" => 'warning',
                                'msg' => '网络错误！请重试'
                            ];
                        }
                  /*  } else {
                        return [
                            "success" => 'warning',
                            'msg' => '网络错误！请重试'
                        ];
                    }*/
                  
                } else {
                    return [
                        "success" => 'warning',
                        "msg" => $validate->getError()
                    ];
                }
            } else {
                return [
                    "success" => 'warning',
                    'msg' => '请填充数据'
                ];
            }
        } else {
            return [
                "success" => 'warning',
                'msg' => '网络错误！请重试'
            ];
        }
    }

    //点击下载
    public function downFile($path = '', $fileName = 'file')
    {
        //
        if (!$path) header("Location: /");
        download(ROOT_PATH . 'public' . $path, $fileName);
    }

    //一级出货数据日志
    public function log()
    {
        $where = $this->request->post();
        $this->assign('where', $where);
        $where = self::str1($where, 'date');
        $page = empty($where['pages']) ? 1 : $where['pages'];
        $list = empty($where['limts']) ? 10 : $where['limts'];
        unset($where['pages']);
        unset($where['limts']);
        //总条数
        $count = Db::table('bx_fy_log')->where($where)->count();
        $this->assign('count', $count);
        //分页
        $maxpage = ceil($count / $list);
        $page = $page < $maxpage ? $page : $maxpage;
        $page = $page > 1 ? $page : 1;
        $start = ($page - 1) * $list;
        $this->assign('page', $page);
        $this->assign('maxpage', $maxpage);
        $this->assign('limit', $list);
        //数据
        $data = Db::table('bx_fy_log')
            ->where($where)->order('id', 'desc')
            ->limit($start, $list)
            ->select();
        $this->assign('data', $data);
        return $this->fetch();
    }

    //log处理数组
    public function str1($where, $field)
    {
        $where = array_filter($where);
        if (isset($where['n']) && isset($where['y'])) {
            $firstDate = $where['n'] . '-' . $where['y'] . '-01 00:00:00';
            $lastDate = date('Y-m-d', strtotime("$firstDate +1 month -1 day")) . ' 23:59:59';
            $where[$field] = ['between', [$firstDate, $lastDate]];
        } else if (isset($where['n'])) {
            $firstDate = $where['n'] . '-01-01 00:00:00';
            $lastDate = $where['n'] . '-12-31 23:59:59';
            $where[$field] = ['between', [$firstDate, $lastDate]];
        }
        unset($where['n']);
        unset($where['y']);
        return $where;
    }

    // 历史出货数据导入
    public function historyImport()
    {
        $where = $this->request->post();
        $where1 = $where;
        if (empty($where)) $where = ['n' => date('Y'), 'y' => date('m')];
        $this->assign('where', $where);
        $data['ny'] = $where['n'] . '年' . $where['y'] . '月';
        $where = self::str2($where, 'fyDate');
        $data['sum'] = Db::table('bx_fy')
            ->where($where)
            ->field('sum(fyZheMoney) fyZheMoney,count(*) count')
            ->find();
        if (!$data['sum']['fyZheMoney']) $data['sum']['fyZheMoney'] = 0;
        $data['user'] = Db::table('employee')->where('id', session('employee_id'))->value('em_name');
        $data['date'] = Db::table('bx_fy_log')->field('date,operator')->order('date', 'desc')->limit(1)->find();
        if (!$data['date']) {
            $data['date'] = ['date' => '还没有更新', 'operator' => '还没有更新'];
        }
        $this->assign('data', $data);
        //所有数据。。。最近三年的
        if (empty($where1)) {
            $firstDate = (date('Y') - 3) . '-01-01 00:00:00';
            $lastDate = date('Y-m-d H:i:s');
            $where1['a.date'] = ['between', [$firstDate, $lastDate]];
        } else {
            $where1 = self::str1($where1, 'a.date');
        }
        $data1 = Db::table('bx_fy_log')->alias('a')
            ->join('bx_fy_log_des b', 'a.id = b.pid', 'LEFT')
            ->field("sum(a.rows) rows,sum(a.zmoney) zmoney,a.id,REPLACE(a.ny,'-','年') ny,b.des,b.desUser")->group('a.ny')->where($where1)->select();
        $this->assign('data1', $data1);
        return $this->fetch();
    }

    //历史导入数据处理数组
    public function str2($where, $field)
    {
        $where = array_filter($where);
        if (isset($where['n']) && isset($where['y'])) {
            $firstDate = $where['n'] . '-' . $where['y'] . '-01';
            $lastDate = date('Y-m-d', strtotime("$firstDate +1 month -1 day"));
            $where[$field] = ['between', [$firstDate, $lastDate]];
        } else if (isset($where['n'])) {
            $firstDate = $where['n'] . '-01-01';
            $lastDate = $where['n'] . '-12-31';
            $where[$field] = ['between', [$firstDate, $lastDate]];
        }
        unset($where['n']);
        unset($where['y']);
        return $where;
    }

    //新增，编辑备注
    public function des()
    {
        $data = $this->request->post();
        $data['desUser'] = Db::table('employee')->where('id', session('employee_id'))->value('em_name');
        if ($id = Db::table('bx_fy_log_des')->where('pid', $data['pid'])->value('id')) {
            Db::table('bx_fy_log_des')->where('id', $id)->update($data);
            return json([
                'success' => true
            ]);
        } else {
            if (Db::table('bx_fy_log_des')->insert($data)) {
                return json([
                    'success' => true
                ]);
            } else {
                return json([
                    'success' => false,
                    'msg' => '网络错误！请重试'
                ]);
            }

        }
    }

    //一级出货数据查询
    public function query()
    {
        //查询条件
        $where = $this->request->post();
        $this->assign('where', $where);
        $where = self::str($where);
        $page = empty($where['pages']) ? 1 : $where['pages'];
        $list = empty($where['limts']) ? 10 : $where['limts'];
        unset($where['pages']);
        unset($where['limts']);
        //总数量，总金额，总条数
        //产品->SKU->产品系列->
//        $tmp = Db::table('bx_fy')->alias('a')->alias('a')
//            ->join('pro_products d', 'a.product_id = d.id')//产品
//            ->join('pro_sku i', 'd.sku_id = i.id')//SKU
//            ->join('pro_series j', 'i.series_id = j.id')//系列
//            ->join('pro_category k', 'j.category_id = k.id')//品类
//            ->join('pro_brands l', 'k.brand_id = l.id')//品牌
//            ->field('a.product_id,d.pro_name,d.id,i.sku_name,j.series_name,k.category_name,l.brand_name')
//            ->where($where)->select();
        $sumCount = Db::table('bx_fy')->alias('a')
            ->join('cg_jxs b', 'a.sdAgency_id = b.id')
            ->join('cg_net c', 'a.sdNet_id = c.id', 'LEFT')
            ->join('pro_products d', 'a.product_id = d.id')
            ->join('pro_sku i', 'd.sku_id = i.id')//SKU
            ->join('pro_series j', 'i.series_id = j.id')//系列
            ->join('pro_category k', 'j.category_id = k.id')//品类
            ->join('pro_brands l', 'k.brand_id = l.id')//品牌
            ->join('employee e', 'a.salesman_id = e.id')
            ->join('cg_market_city f', 'c.city = f.id', 'LEFT')
            ->join('cg_market_provice g', 'c.provice = g.id', 'LEFT')
            ->join('cg_market_zoning h', 'c.zoning = h.id', 'LEFT')
            ->where($where)
            ->field('sum(fyNum) fyNum,sum(fyZheMoney) fyZheMoney,count(*) count')->find();
        $this->assign('sumCount', $sumCount);
        //分页
        $maxpage = ceil($sumCount['count'] / $list);
        $page = $page < $maxpage ? $page : $maxpage;
        $page = $page > 1 ? $page : 1;
        $start = ($page - 1) * $list;
        $this->assign('page', $page);
        $this->assign('maxpage', $maxpage);
        $this->assign('limit', $list);
        //数据
        $data = Db::table('bx_fy')->alias('a')
            ->join('cg_jxs b', 'a.sdAgency_id = b.id')
            ->join('cg_net c', 'a.sdNet_id = c.id', 'LEFT')
            ->join('pro_products d', 'a.product_id = d.id')
            ->join('pro_sku i', 'd.sku_id = i.id')//SKU
            ->join('pro_series j', 'i.series_id = j.id')//系列
            ->join('pro_category k', 'j.category_id = k.id')//品类
            ->join('pro_brands l', 'k.brand_id = l.id')//品牌
            ->join('employee e', 'a.salesman_id = e.id')
            ->join('cg_market_city f', 'c.city = f.id', 'LEFT')
            ->join('cg_market_provice g', 'c.provice = g.id', 'LEFT')
            ->join('cg_market_zoning h', 'c.zoning = h.id', 'LEFT')
            ->field('a.*,b.title jxs,b.sn jxsNo,c.title wd,c.sn wdNo,d.pro_name,d.pro_no,
            e.em_name,f.title shi,g.title sheng,h.title qu')
            ->where($where)->order('id', 'desc')
            ->limit($start, $list)
            ->select();
        $this->assign('data', $data);
        return $this->fetch();
    }

    public function xml()
    {
        //  加载XML内容
        $xml = simplexml_load_file(ROOT_PATH . 'public/static/fy/info.xml');
        foreach ($xml->data as $tmp) {
            $data = $tmp->attributes();
            echo "id：" . $data['id'] . "&nbsp;名称：" . $data['name'] . '<br>';
        }
//        foreach($xml as $tmp){
//            echo $tmp.'<br>';
//        }
        return $this->fetch();
    }

    //php获取中文字符拼音首字母
    function getFirstCharter($str)
    {
        if (empty($str)) {
            return '#';
        }
        $fchar = ord($str{0});
        if ($fchar >= ord('A') && $fchar <= ord('z')) return strtoupper($str{0});
        $s1 = iconv('UTF-8', 'gb2312', $str);
        $s2 = iconv('gb2312', 'UTF-8', $s1);
        $s = $s2 == $str ? $s1 : $str;
        $asc = ord($s{0}) * 256 + ord($s{1}) - 65536;
        if ($asc >= -20319 && $asc <= -20284) return 'A';
        if ($asc >= -20283 && $asc <= -19776) return 'B';
        if ($asc >= -19775 && $asc <= -19219) return 'C';
        if ($asc >= -19218 && $asc <= -18711) return 'D';
        if ($asc >= -18710 && $asc <= -18527) return 'E';
        if ($asc >= -18526 && $asc <= -18240) return 'F';
        if ($asc >= -18239 && $asc <= -17923) return 'G';
        if ($asc >= -17922 && $asc <= -17418) return 'H';
        if ($asc >= -17417 && $asc <= -16475) return 'J';
        if ($asc >= -16474 && $asc <= -16213) return 'K';
        if ($asc >= -16212 && $asc <= -15641) return 'L';
        if ($asc >= -15640 && $asc <= -15166) return 'M';
        if ($asc >= -15165 && $asc <= -14923) return 'N';
        if ($asc >= -14922 && $asc <= -14915) return 'O';
        if ($asc >= -14914 && $asc <= -14631) return 'P';
        if ($asc >= -14630 && $asc <= -14150) return 'Q';
        if ($asc >= -14149 && $asc <= -14091) return 'R';
        if ($asc >= -14090 && $asc <= -13319) return 'S';
        if ($asc >= -13318 && $asc <= -12839) return 'T';
        if ($asc >= -12838 && $asc <= -12557) return 'W';
        if ($asc >= -12556 && $asc <= -11848) return 'X';
        if ($asc >= -11847 && $asc <= -11056) return 'Y';
        if ($asc >= -11055 && $asc <= -10247) return 'Z';
        return '#';
    }

    //非常规货补清单查询
    public function repairGoods()
    {
        //查询条件
        $where = $this->request->post();
        $this->assign('where', $where);
        $where = self::str($where);
        $page = empty($where['pages']) ? 1 : $where['pages'];
        $list = empty($where['limts']) ? 10 : $where['limts'];
        unset($where['pages']);
        unset($where['limts']);
        //总数量，总金额，总条数
        $sumCount = Db::table('cg_order')->alias('a')
            ->join('employee b', 'a.sqr = b.id','LEFT')
            ->join('cg_order_huobu c', 'a.id = c.order_id','LEFT')
            ->join('cg_ti_hexiao_list d', 'c.hxdmxbh = d.id','LEFT')
            ->join('cg_jxs e', 'a.cusName = e.id','LEFT')
            ->join('cg_market_city g', 'e.city = g.id', 'LEFT')
            ->join('cg_market_provice h', 'e.provice = h.id', 'LEFT')
            ->join('cg_market_zoning j', 'e.zoning = j.id', 'LEFT')
            ->join('cg_net f', 'a.cusName = f.id', 'LEFT')
            ->join('cg_market_city k', 'f.city = k.id', 'LEFT')
            ->join('cg_market_provice l', 'f.provice = l.id', 'LEFT')
            ->join('cg_market_zoning m', 'f.zoning = m.id', 'LEFT')
            ->join('pro_products n', 'c.prod_id = n.id','LEFT')
            ->join('pro_materials o', 'c.prod_id = o.id','LEFT')
            ->where($where)
            ->field('sum(c.prod_price) dj,sum(c.num) num,sum(c.prod_jine) ze,count(*) count')->find();
        $this->assign('sumCount', $sumCount);
        //分页
        $maxpage = ceil($sumCount['count'] / $list);
        $page = $page < $maxpage ? $page : $maxpage;
        $page = $page > 1 ? $page : 1;
        $start = ($page - 1) * $list;
        $this->assign('page', $page);
        $this->assign('maxpage', $maxpage);
        $this->assign('limit', $list);
        //数据
        $data = Db::table('cg_order')->alias('a')
            ->join('employee b', 'a.sqr = b.id','LEFT')
            ->join('cg_order_huobu c', 'a.id = c.order_id','LEFT')
            ->join('cg_ti_hexiao_list d', 'c.hxdmxbh = d.id','LEFT')
            ->join('cg_jxs e', 'a.cusName = e.id','LEFT')
            ->join('cg_market_city g', 'e.city = g.id', 'LEFT')
            ->join('cg_market_provice h', 'e.provice = h.id', 'LEFT')
            ->join('cg_market_zoning j', 'e.zoning = j.id', 'LEFT')
            ->join('cg_net f', 'a.cusName = f.id', 'LEFT')
            ->join('cg_market_city k', 'f.city = k.id', 'LEFT')
            ->join('cg_market_provice l', 'f.provice = l.id', 'LEFT')
            ->join('cg_market_zoning m', 'f.zoning = m.id', 'LEFT')
            ->join('pro_products n', 'c.prod_id = n.id','LEFT')
            ->join('pro_materials o', 'c.prod_id = o.id','LEFT')
            ->field('b.em_name,a.sn,
            g.title shi1,h.title sheng1,j.title qu1,k.title shi2,l.title sheng2,m.title qu2,
            a.cusType,e.title jxs,f.title net,
            c.kind,n.pro_name wln1,n.pro_no wlb1,n.unit unit1,o.material_name wln2,o.material_no wlb2,o.unit unit2,
            a.shr,
            c.prod_price,c.num,c.prod_jine,
            d.gdrq,
            a.id')
            ->where($where)->order('id', 'desc')
            ->limit($start, $list)
            ->select();
        foreach ($data as $key => $val){
            if(!$val['cusType']) $val['cusType'] = 1;
            $data[$key]['shi'] = $val['shi'.$val['cusType']];
            $data[$key]['sheng'] = $val['sheng'.$val['cusType']];
            $data[$key]['qu'] = $val['qu'.$val['cusType']];
            if($val['cusType'] = 1){
                $data[$key]['net'] = '';
            }else{
                $data[$key]['jxs'] = '';
            }
            if(!$val['kind']) $val['kind'] = 1;
            $data[$key]['wln'] = $val['wln'.$val['kind']];
            $data[$key]['wlb'] = $val['wlb'.$val['kind']];
            $data[$key]['unit'] = $val['unit'.$val['kind']];
            $data[$key]['kind'] = $val['kind'] = 1 ? '样品' : '物料';
            $data[$key]['hexiao'] = empty($val['gdrq'])  ? '否' : '是';
        }
        $this->assign('data', $data);
        return $this->fetch('ashippingmanagement/repairGoods');
    }


    //导出一级出货数据
    public function exportGoods()
    {
        $user = Db::table('employee')->where('id', session('employee_id'))->value('em_name');
        $str = '注：本表单由销路通软件导出，导出时间：' . date('Y-m-d H:i:s') . '；导出人：' . $user . '；金额单位：元';
        $str1 = '非常规货补清单汇总';
//        $titles = ['姓名', '年龄', '性别', '身高'];
        // titles格式也可以如下
        $titles = [
            'No' => 'No.',
            'em_name' => '申请人',
            'sn' => '申请编号',
            'jxs' => '经销商',
            'net' => '网点',
            'kind' => '物料类型',
            'shr' => '收货方名称',
            'prod_price' => '单价',
            'num' => '数量',
            'prod_jine' => '金额',
            'gdrq' => '核销时间',
            'id' => '发货单号',
            'shi' => '城市',
            'sheng' => '省份',
            'qu' => '分区',
            'wln' => '物料名称',
            'wlb' => '物料编码',
            'unit' => '单位',
            'hexiao' => '是否已核销',
        ];
        Db::query("set @i = 0");//数据库自动增加序号
        $data = Db::table('cg_order')->alias('a')
            ->join('employee b', 'a.sqr = b.id','LEFT')
            ->join('cg_order_huobu c', 'a.id = c.order_id','LEFT')
            ->join('cg_ti_hexiao_list d', 'c.hxdmxbh = d.id','LEFT')
            ->join('cg_jxs e', 'a.cusName = e.id','LEFT')
            ->join('cg_market_city g', 'e.city = g.id', 'LEFT')
            ->join('cg_market_provice h', 'e.provice = h.id', 'LEFT')
            ->join('cg_market_zoning j', 'e.zoning = j.id', 'LEFT')
            ->join('cg_net f', 'a.cusName = f.id', 'LEFT')
            ->join('cg_market_city k', 'f.city = k.id', 'LEFT')
            ->join('cg_market_provice l', 'f.provice = l.id', 'LEFT')
            ->join('cg_market_zoning m', 'f.zoning = m.id', 'LEFT')
            ->join('pro_products n', 'c.prod_id = n.id','LEFT')
            ->join('pro_materials o', 'c.prod_id = o.id','LEFT')
            ->field('(@i:=@i+1) No,b.em_name,a.sn,
            g.title shi1,h.title sheng1,j.title qu1,k.title shi2,l.title sheng2,m.title qu2,
            a.cusType,e.title jxs,f.title net,
            c.kind,n.pro_name wln1,n.pro_no wlb1,n.unit unit1,o.material_name wln2,o.material_no wlb2,o.unit unit2,
            a.shr,
            c.prod_price,c.num,c.prod_jine,
            d.gdrq,
            a.id')
            ->select();
        foreach ($data as $key => $val){
            if(!$val['cusType']) $val['cusType'] = 1;
            $data[$key]['shi'] = $val['shi'.$val['cusType']];
            $data[$key]['sheng'] = $val['sheng'.$val['cusType']];
            $data[$key]['qu'] = $val['qu'.$val['cusType']];
            if($val['cusType'] = 1){
                $data[$key]['net'] = '';
            }else{
                $data[$key]['jxs'] = '';
            }
            if(!$val['kind']) $val['kind'] = 1;
            $data[$key]['wln'] = $val['wln'.$val['kind']];
            $data[$key]['wlb'] = $val['wlb'.$val['kind']];
            $data[$key]['unit'] = $val['unit'.$val['kind']];
            $data[$key]['kind'] = $val['kind'] = 1 ? '样品' : '物料';
            $data[$key]['hexiao'] = empty($val['gdrq'])  ? '否' : '是';
            unset($data[$key]['cusType']);
            unset($data[$key]['shi1']);unset($data[$key]['shi2']);unset($data[$key]['sheng1']);unset($data[$key]['sheng2']);unset($data[$key]['qu1']);unset($data[$key]['qu2']);
            unset($data[$key]['wln1']);unset($data[$key]['wln2']);unset($data[$key]['wlb1']);unset($data[$key]['wlb2']);
            unset($data[$key]['unit1']);unset($data[$key]['unit2']);
        }
        $data[] = [
            'title' => $str1,
            'sub' => $str,
        ];
        $excel = new ExcelDownload();
        $excel->exec($data, $titles, '非常规货补清单汇总', ['sheet' => ['rowHeight' => 20]], 3);
    }
}
